﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using AutoWelding.Models;

using MySql.Data.MySqlClient;

namespace AutoWelding.DAL
{
    public class RecipesDAL
    {
        /// <summary>
        /// 获取所有数据
        /// </summary>
        public List<RecipesModel> GetRecipesList(string recipeName,int productId,int isDeleteCode)
        {
            string selectSql = "select * from recipes where 1=1 ";
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            if (!string.IsNullOrEmpty(recipeName) || recipeName != "")
            {
                selectSql += "and recipe_name like @RecipeName ";
                parameters.Add(new MySqlParameter("@RecipeName", MySqlDbType.VarChar) { Value = "%" + recipeName + "%" });
            }
            if (productId > 0)
            {
                selectSql += "and product_id = @ProductId ";
                parameters.Add(new MySqlParameter("@ProductId", MySqlDbType.Int32) { Value = productId });
            }
            selectSql += "and is_delete = @IsDelete order by create_time desc";
            parameters.Add(new MySqlParameter("@IsDelete", MySqlDbType.Int32) { Value = isDeleteCode });
            Console.Write("查询配方的语句" + selectSql);

            return MysqlHelp.ExecuteQuery<RecipesModel>(selectSql,
                reader => new RecipesModel 
                { 
                   RecipeId = Convert.ToInt32(reader["recipe_id"]),
                   RecipeName = reader["recipe_name"].ToString(),
                   ProductId = Convert.ToInt32(reader["product_id"]),
                   //Recipe01 = reader["recipe_01"].ToString(),
                   //Recipe02 = reader["recipe_02"].ToString(),
                   //其他省略不写
                   CreateTime = Convert.ToDateTime(reader["create_time"]),
                   //UpdateTime = Convert.ToDateTime(reader["update_time"]),
                   Remark = reader["remark"].ToString()

                }, parameters.ToArray());
        }

        /// <summary>
        /// 根据ID获取数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public RecipesModel GetRecipeById(int id)
        {
            string selectSql = "select * from recipes where recipe_id = @Id";
            DataTable dt = MysqlHelp.ExecuteQuery(selectSql, new MySqlParameter("@Id", MySqlDbType.Int32) { Value = id });
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                return new RecipesModel
                {
                    RecipeId = Convert.ToInt32(row["recipe_id"]),
                    RecipeName = SafeGetString(row, "recipe_name"),
                    ProductId = SafeGetInt(row, "product_id"),

                    // 填充 Recipe01 - Recipe20
                    WeldingNum = SafeGetString(row, "weldingNum"),
                    Recipe02 = SafeGetString(row, "recipe_02"),
                    Recipe03 = SafeGetString(row, "recipe_03"),
                    Recipe04 = SafeGetString(row, "recipe_04"),
                    Recipe05 = SafeGetString(row, "recipe_05"),
                    Recipe06 = SafeGetString(row, "recipe_06"),
                    Recipe07 = SafeGetString(row, "recipe_07"),
                    Recipe08 = SafeGetString(row, "recipe_08"),
                    Recipe09 = SafeGetString(row, "recipe_09"),
                    Recipe10 = SafeGetString(row, "recipe_10"),

                    Recipe11 = SafeGetString(row, "recipe_11"),
                    Recipe12 = SafeGetString(row, "recipe_12"),
                    Recipe13 = SafeGetString(row, "recipe_13"),
                    Recipe14 = SafeGetString(row, "recipe_14"),
                    Recipe15 = SafeGetString(row, "recipe_15"),
                    Recipe16 = SafeGetString(row, "recipe_16"),
                    Recipe17 = SafeGetString(row, "recipe_17"),
                    Recipe18 = SafeGetString(row, "recipe_18"),
                    Recipe19 = SafeGetString(row, "recipe_19"),
                    Recipe20 = SafeGetString(row, "recipe_20"),

                    CreateTime = (DateTime)SafeGetDateTime(row, "create_time"),
                    UpdateTime = SafeGetDateTime(row, "update_time"),
                    Remark = SafeGetString(row, "remark")
                };
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 添加配方数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InsertRecipe(RecipesModel model)
        {
            if (model == null)
                throw new ArgumentNullException(nameof(model));

            string insertSql = $@"
                                INSERT INTO recipes (
                                recipe_name, product_id,
                                weldingNum, recipe_02, recipe_03, recipe_04, recipe_05,
                                recipe_06, recipe_07, recipe_08, recipe_09, recipe_10,
                                recipe_11, recipe_12, recipe_13, recipe_14, recipe_15,
                                recipe_16, recipe_17, recipe_18, recipe_19, recipe_20,
                                create_time, update_time, remark, is_delete
                                ) VALUES (
                                @RecipeName, @ProductId,
                                @WeldingNum, @Recipe02, @Recipe03, @Recipe04, @Recipe05,
                                @Recipe06, @Recipe07, @Recipe08, @Recipe09, @Recipe10,
                                @Recipe11, @Recipe12, @Recipe13, @Recipe14, @Recipe15,
                                @Recipe16, @Recipe17, @Recipe18, @Recipe19, @Recipe20,
                                @CreateTime, @UpdateTime, @Remark, @IsDelete
                                );
                                SELECT LAST_INSERT_ID();";
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter("@RecipeId", MySqlDbType.Int32) { Value = model.RecipeId },
                //配方名称
                new MySqlParameter("@RecipeName", MySqlDbType.VarChar) { Value = model.RecipeName },
                //产品ID
                new MySqlParameter("@ProductId", MySqlDbType.Int32) { Value = model.ProductId },
                //配方数据
                new MySqlParameter("@WeldingNum", MySqlDbType.VarChar) { Value = model.WeldingNum},
                new MySqlParameter("@Recipe02", MySqlDbType.VarChar) { Value = model.Recipe02},
                new MySqlParameter("@Recipe03", MySqlDbType.VarChar) { Value = model.Recipe03},
                new MySqlParameter("@Recipe04", MySqlDbType.VarChar) { Value = model.Recipe04},
                new MySqlParameter("@Recipe05", MySqlDbType.VarChar) { Value = model.Recipe05},
                new MySqlParameter("@Recipe06", MySqlDbType.VarChar) { Value = model.Recipe06},
                new MySqlParameter("@Recipe07", MySqlDbType.VarChar) { Value = model.Recipe07 },
                new MySqlParameter("@Recipe08", MySqlDbType.VarChar) { Value = model.Recipe08 },
                new MySqlParameter("@Recipe09", MySqlDbType.VarChar) { Value = model.Recipe09 },
                new MySqlParameter("@Recipe10", MySqlDbType.VarChar) { Value = model.Recipe10 },
                new MySqlParameter("@Recipe11", MySqlDbType.VarChar) { Value = model.Recipe11 },
                new MySqlParameter("@Recipe12", MySqlDbType.VarChar) { Value = model.Recipe12 },
                new MySqlParameter("@Recipe13", MySqlDbType.VarChar) { Value = model.Recipe13 },
                new MySqlParameter("@Recipe14", MySqlDbType.VarChar) { Value = model.Recipe14 },
                new MySqlParameter("@Recipe15", MySqlDbType.VarChar) { Value = model.Recipe15 },
                new MySqlParameter("@Recipe16", MySqlDbType.VarChar) { Value = model.Recipe16 },
                new MySqlParameter("@Recipe17", MySqlDbType.VarChar) { Value = model.Recipe17 },
                new MySqlParameter("@Recipe18", MySqlDbType.VarChar) { Value = model.Recipe18 },
                new MySqlParameter("@Recipe19", MySqlDbType.VarChar) { Value = model.Recipe19 },
                new MySqlParameter("@Recipe20", MySqlDbType.VarChar) { Value = model.Recipe20 },
                //时间
                new MySqlParameter("@UpdateTime", MySqlDbType.DateTime){Value = model.UpdateTime},
                new MySqlParameter("@CreateTime",MySqlDbType.DateTime){ Value = model.CreateTime},
                //备注
                new MySqlParameter("@Remark", MySqlDbType.VarChar) { Value = model.Remark },
                //删除
                new MySqlParameter("@IsDelete", MySqlDbType.Int32) { Value = model.IsDelete }
            };

            int result = MysqlHelp.ExecuteNonQuery(insertSql, parameters.ToArray());
            return result;

        }

        /// <summary>
        /// 修改配方数据
        /// </summary>
        public int UpdateRecipe(RecipesModel model)
        {
            string updateSql = "update recipes set " +
                "recipe_name = @RecipeName," +
                "product_id = @ProductId," +
                "weldingNum = @WeldingNum,recipe_02 = @Recipe02,recipe_03 = @Recipe03,recipe_04 = @Recipe04,recipe_05 = @Recipe05,recipe_06 = @Recipe06,recipe_07 = @Recipe07,recipe_08 = @Recipe08,recipe_09 = @Recipe09,recipe_10 = @Recipe10," +
                "recipe_11 = @Recipe11,recipe_12 = @Recipe12,recipe_13 = @Recipe13,recipe_14 = @Recipe14,recipe_15 = @Recipe15,recipe_16 = @Recipe16,recipe_17 = @Recipe17,recipe_18 = @Recipe18,recipe_19 = @Recipe19,recipe_20 = @Recipe20," +
                "update_time = @UpdateTime," +
                "remark = @Remark," +
                "is_delete = @IsDelete" +
                " where recipe_id = @RecipeId";
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter("@RecipeId", MySqlDbType.Int32) { Value = model.RecipeId },
                //配方名称
                new MySqlParameter("@RecipeName", MySqlDbType.VarChar) { Value = model.RecipeName },
                //产品ID
                new MySqlParameter("@ProductId", MySqlDbType.Int32) { Value = model.ProductId },
                //配方数据
                new MySqlParameter("@WeldingNum", MySqlDbType.VarChar) { Value = model.WeldingNum},
                new MySqlParameter("@Recipe02", MySqlDbType.VarChar) { Value = model.Recipe02},
                new MySqlParameter("@Recipe03", MySqlDbType.VarChar) { Value = model.Recipe03},
                new MySqlParameter("@Recipe04", MySqlDbType.VarChar) { Value = model.Recipe04},
                new MySqlParameter("@Recipe05", MySqlDbType.VarChar) { Value = model.Recipe05},
                new MySqlParameter("@Recipe06", MySqlDbType.VarChar) { Value = model.Recipe06},
                new MySqlParameter("@Recipe07", MySqlDbType.VarChar) { Value = model.Recipe07 },
                new MySqlParameter("@Recipe08", MySqlDbType.VarChar) { Value = model.Recipe08 },
                new MySqlParameter("@Recipe09", MySqlDbType.VarChar) { Value = model.Recipe09 },
                new MySqlParameter("@Recipe10", MySqlDbType.VarChar) { Value = model.Recipe10 },
                new MySqlParameter("@Recipe11", MySqlDbType.VarChar) { Value = model.Recipe11 },
                new MySqlParameter("@Recipe12", MySqlDbType.VarChar) { Value = model.Recipe12 },
                new MySqlParameter("@Recipe13", MySqlDbType.VarChar) { Value = model.Recipe13 },
                new MySqlParameter("@Recipe14", MySqlDbType.VarChar) { Value = model.Recipe14 },
                new MySqlParameter("@Recipe15", MySqlDbType.VarChar) { Value = model.Recipe15 },
                new MySqlParameter("@Recipe16", MySqlDbType.VarChar) { Value = model.Recipe16 },
                new MySqlParameter("@Recipe17", MySqlDbType.VarChar) { Value = model.Recipe17 },
                new MySqlParameter("@Recipe18", MySqlDbType.VarChar) { Value = model.Recipe18 },
                new MySqlParameter("@Recipe19", MySqlDbType.VarChar) { Value = model.Recipe19 },
                new MySqlParameter("@Recipe20", MySqlDbType.VarChar) { Value = model.Recipe20 },
                //时间
                new MySqlParameter("@UpdateTime", MySqlDbType.DateTime){Value = model.UpdateTime},
                new MySqlParameter("@CreateTime",MySqlDbType.DateTime){ Value = model.CreateTime},
                //备注
                new MySqlParameter("@Remark", MySqlDbType.VarChar) { Value = model.Remark },
                //删除
                new MySqlParameter("@IsDelete", MySqlDbType.Int32) { Value = model.IsDelete }
            };

            int result = MysqlHelp.ExecuteNonQuery(updateSql, parameters.ToArray());
            return result;
        }

        /// <summary>
        /// 真删除配方数据
        /// </summary>
        public int DeleteRecipe(int id)
        {
            string deleteSql = "DELETE FROM recipes WHERE recipe_id = @id";
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter("@id", MySqlDbType.Int32) { Value = id }
            };
            return MysqlHelp.ExecuteNonQuery(deleteSql, parameters.ToArray());
        }
        /// <summary>
        /// 批量更新 is_delete 字段
        /// </summary>
        /// <param name="recipeIds">要操作的配方ID列表</param>
        /// <param name="isDeleteValue">要设置的 is_delete 值（0 或 1）</param>
        /// <returns>受影响的行数</returns>
        public int BatchUpdateIsDelete(List<int> recipeIds, int isDeleteValue)
        {
            if (recipeIds == null || recipeIds.Count == 0)
                return 0;

            // 构建 SQL
            string sql = $@"
                        UPDATE recipes 
                        SET is_delete = @IsDelete 
                        WHERE recipe_id IN ({string.Join(",", recipeIds.Select(id => $"@id{id}"))})";

            // 构建参数
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            foreach (int id in recipeIds)
            {
                parameters.Add(new MySqlParameter($"@id{id}", MySqlDbType.Int32) { Value = id });
            }
            parameters.Add(new MySqlParameter("@IsDelete", MySqlDbType.Int32) { Value = isDeleteValue });

            // 执行 SQL
            return MysqlHelp.ExecuteNonQuery(sql, parameters.ToArray());
        }

        #region 安全读取字段方法封装
        private string SafeGetString(DataRow row, string columnName)
        {
            return row.Table.Columns.Contains(columnName) && row[columnName] != DBNull.Value
                ? row[columnName].ToString()
                : string.Empty;
        }

        private int SafeGetInt(DataRow row, string columnName)
        {
            return row.Table.Columns.Contains(columnName) && row[columnName] != DBNull.Value
                ? Convert.ToInt32(row[columnName])
                : 0;
        }

        private DateTime? SafeGetDateTime(DataRow row, string columnName)
        {
            if (row.IsNull(columnName))
                return null;

            return Convert.ToDateTime(row[columnName]);
        }
        #endregion
    }
}
